/*
	This Procedure loads all the Running, Hold and Shipped Lots Inventory in Chiptrac.
 	EXEC sp_inventory_summary_report_proc_latest 0,'0','0','0','0','0','FAB,Wafer Bank,Fab Option,Die Bank,Assembly,FT,FPO,Tape & Reel,FG'
*/

ALTER  PROC sp_inventory_summary_report_proc_latest

	@PRODUCT_ID 	INT,
	@LOT_CATEGORY	VARCHAR(50),
	@VENDOR		VARCHAR(50),
	@STATUS		VARCHAR(50),
	@ASOFDATE	VARCHAR(50),
	@DEFAULT_PROMPT	VARCHAR(50),
	@PS_TYPES		VARCHAR(200)
AS
BEGIN

CREATE TABLE #INVENTORY_SUMMARY_TABLE ( FAB_LOT_NUMBER VARCHAR(50),LOT_NUMBER VARCHAR(50),WAFER_QTY_IN INT,QTY_IN INT,
					--LOT_CATEGORY VARCHAR(50),VENDOR_NAME VARCHAR(50),
					PART_NUMBER VARCHAR(50), PROCESSSTEP_ID INT,BASE_PRODUCT VARCHAR(50),--PS_TYPE_ID INT,
					PS_TYPE VARCHAR(50),GROSS_DIE INT, TRA_WAFER_QTY_IN INT,TRA_QTY_IN INT )

DECLARE @RESULTS TABLE ( [BASE PRODUCT] VARCHAR(50),[PART NUMBER] VARCHAR(50),[QTY STR] VARCHAR(200),[DIE QTY] INT,[WAFER DIE QTY] INT )

DECLARE @SQLSTRING 		NVARCHAR(4000)
DECLARE @SQLSTRING1 	NVARCHAR(3000)

DECLARE @BASE_PRODUCT	VARCHAR(50)
DECLARE @PART_NUMBER 	VARCHAR(50)
DECLARE @PS_TYPE 		VARCHAR(50)
DECLARE @PS_TYPE_NAME   VARCHAR(50)
DECLARE @PACKAGE_TYPE 	VARCHAR(50)
DECLARE @WAFER_QTY_IN 	INT
DECLARE @QTY_IN 		INT
DECLARE @TRA_WAFER_QTY_IN 	INT
DECLARE @TRA_QTY_IN 		INT

DECLARE @GROSS_DIE		INT
DECLARE @DIE_TOTAL_FOR_WAFERS	INT
DECLARE @IDX INT
DECLARE @QTY INT

DECLARE @TEMP_VARCHAR 	VARCHAR(50)
DECLARE @START_DATE 	DATETIME
DECLARE @END_DATE 	DATETIME

DECLARE @LOT_CURSOR 		CURSOR

DECLARE @PS_TYPE_NAMES VARCHAR(200)
SET @PS_TYPE_NAMES = @PS_TYPES

	SET @SQLSTRING = N'INSERT INTO #INVENTORY_SUMMARY_TABLE
		SELECT  FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, WAFER_QTY_IN, QTY_IN,--LOT_CATEGORY,L_VENDOR_NAME, 
		PART_NUMBER,P.PROCESSSTEP_ID,
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
			CASE 
				WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
				WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
			END 
		) AS BASE_PRODUCT,PS_TYPE,
		( CASE 
			WHEN PS_TYPE_NAME = ''FAB''  THEN  convert(int,cast((SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''Gross Die/Wafer'' AND PROCESSSTEP_ID = LT.PROCESSSTEP_ID) as float))
			WHEN PS_TYPE_NAME = ''WAFER STORE'' THEN convert(int,cast((SELECT PARAMETER_VALUE FROM Process_Step_Traverse_Table PST,PROCESS_PARAMETER PP WHERE PST.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND PARAMETER_KEY = ''Gross Die/Wafer'' AND NEXT_PROCESSSTEP_ID = LT.PROCESSSTEP_ID) as float))
			ELSE NULL
		END ), NULL AS TRA_WAFER_QTY_IN,NULL AS TRA_QTY_IN '
	
	IF @ASOFDATE = '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + '	FROM LOT_TABLE LT,PRODUCT P, PROCESS_STEP PS, LOCATION L '
	END
	ELSE
	BEGIN
		SET @SQLSTRING = @SQLSTRING + '	FROM LOT_TABLE_HISTORY LT,PRODUCT P, PROCESS_STEP PS, LOCATION L '

		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
	END
		SET @SQLSTRING = @SQLSTRING + ' WHERE 
		LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
		AND LT.LOCATION_ID = L.LOCATION_ID
		--AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' )
		AND P_TYPE IS NULL
		AND P.OBSOLETE != 0 '

	IF @ASOFDATE <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.TimeStamp = ( SELECT MAX(LTH.Timestamp) FROM LOT_TABLE_HISTORY LTH WHERE lth.lot_id = lt.lot_id and LTH.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''') '
	END

	IF @PRODUCT_ID <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND P.P_PARENT_PRODUCT_ID = ' + convert(varchar,@PRODUCT_ID) 
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ISNULL(LT.LOT_CATEGORY, ''PROD'') =  ''' +  @LOT_CATEGORY + '''' 
		--SET @SQLSTRING = @SQLSTRING + ' AND LT.LOT_CATEGORY =  ''' +  @LOT_CATEGORY + '''' 
	END 
	IF @VENDOR <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND L.L_VENDOR_NAME =  ''' +  @VENDOR + '''' 
	END
	IF @STATUS <> '0'
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND LT.LOT_CURRENT_STATUS =  ''' +  @STATUS + '''' 
	END
	ELSE
	BEGIN
		SET @SQLSTRING = @SQLSTRING + ' AND ( LOT_CURRENT_STATUS = ''RUNNING'' OR LOT_CURRENT_STATUS = ''HOLD'' )' 
	END	
	
	SET @SQLSTRING1 = N' UNION
	SELECT FAB_LOT_NUMBER, VENDOR_LOT_NUMBER,NULL,NULL,--LOT_CATEGORY,L_VENDOR_NAME,
		 PART_NUMBER,P.PROCESSSTEP_ID,
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
			CASE 
				WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
				WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
			END 
		) AS BASE_PRODUCT, PS_TYPE,
		( CASE 
			WHEN PS_TYPE_NAME = ''FAB''  THEN  convert(int,cast((SELECT PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = ''Gross Die/Wafer'' AND PROCESSSTEP_ID = LT.PROCESSSTEP_ID) as float))
			WHEN PS_TYPE_NAME = ''WAFER STORE'' THEN convert(int,cast((SELECT PARAMETER_VALUE FROM Process_Step_Traverse_Table PST,PROCESS_PARAMETER PP WHERE PST.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
					AND PARAMETER_KEY = ''Gross Die/Wafer'' AND NEXT_PROCESSSTEP_ID = LT.PROCESSSTEP_ID) as float))
			ELSE NULL
		END ),
		(CASE 
			WHEN ( PS_TYPE_NAME = ''FAB'' OR PS_TYPE_NAME = ''WAFER STORE'' OR PS_TYPE_NAME = ''BUMPING'' OR PS_TYPE_NAME = ''SORT'' ) THEN SHIP_QTY 
			ELSE NULL
		END 
		)AS TRA_WAFER_QTY_IN, 
		(CASE 
			WHEN ( PS_TYPE_NAME = ''ASSEMBLY'' OR PS_TYPE_NAME = ''DIE BANK'' OR PS_TYPE_NAME = ''CLASS STORE'' OR PS_TYPE_NAME = ''TEST'' ) THEN SHIP_QTY 
			ELSE NULL
		END 
		)AS TRA_QTY_IN '

	IF @ASOFDATE IS NOT NULL AND @ASOFDATE = '0'
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' FROM 
			SHIPPED_LOT SL, LOT_TABLE LT,LOCATION L, PRODUCT P, PROCESS_STEP PS '
	END
	ELSE
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' FROM 
			SHIPPED_LOT_HISTORY SL, LOT_TABLE LT,LOCATION L, PRODUCT P, PROCESS_STEP PS '
	END

	SET @SQLSTRING1 = @SQLSTRING1 + ' WHERE 
		SL.LOT_ID = LT.LOT_ID
		AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
		AND LT.LOCATION_ID = L.LOCATION_ID
		AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
		AND SL.STATUS = ''TRANSIT''
		AND P_TYPE IS NULL
		AND P.OBSOLETE != 0 '
	
	IF @ASOFDATE <> '0'
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND SL.TimeStamp BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''''
	END
	IF @PRODUCT_ID <> '0'
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + 'AND P.P_PARENT_PRODUCT_ID =  '+  convert(varchar,@PRODUCT_ID)
	END
	IF @LOT_CATEGORY <> '0'
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND LT.LOT_CATEGORY =  ''' +  @LOT_CATEGORY + '''' 
	END
	IF @VENDOR <> '0'
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND L.L_VENDOR_NAME =  ''' +  @VENDOR + '''' 
	END 
		
	--PRINT @SQLSTRING
	--PRINT @SQLSTRING1	

	EXEC(@SQLSTRING + @SQLSTRING1)
	
	INSERT INTO @RESULTS ( [BASE PRODUCT],[PART NUMBER] ) (SELECT DISTINCT BASE_PRODUCT,PART_NUMBER FROM #INVENTORY_SUMMARY_TABLE )
	
	SET @LOT_CURSOR = CURSOR FOR SELECT DISTINCT BASE_PRODUCT,PART_NUMBER from #INVENTORY_SUMMARY_TABLE
			
	OPEN @LOT_CURSOR
	FETCH NEXT FROM @LOT_CURSOR INTO @BASE_PRODUCT,@PART_NUMBER
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @IDX = 1
		SET @PS_TYPE = NULL
		
		WHILE ( @IDX > 0 )
		BEGIN
			IF @IDX = 1 AND @PS_TYPES IS NULL
			BEGIN
				SET @PS_TYPES = @PS_TYPE_NAMES
			END

			SET @IDX = CHARINDEX( ',', @PS_TYPES )
			
			IF @IDX > 0
			BEGIN 
				
				SET @PS_TYPE = SUBSTRING( @PS_TYPES, 1, @IDX-1 )
				SET @PS_TYPES = SUBSTRING ( @PS_TYPES, @IDX+1, LEN ( @PS_TYPES) )
			END
			ELSE
			BEGIN
				SET @PS_TYPE = @PS_TYPES
				SET @PS_TYPES = NULL
			END
			
			IF ( @PS_TYPE IS NOT NULL AND LEN( @PS_TYPE ) > 0 )
			BEGIN
				SELECT @PS_TYPE_NAME = BASE_PROCESS_TYPE FROM PROCESS_TYPES_TABLE WHERE PROCESS_TYPE = @PS_TYPE
				
				IF @PS_TYPE_NAME ='FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT'
				BEGIN
						SELECT @QTY = (ISNULL(SUM(WAFER_QTY_IN),0) + ISNULL(SUM(TRA_WAFER_QTY_IN),0 ) ) FROM #INVENTORY_SUMMARY_TABLE WHERE PS_TYPE = @PS_TYPE
						AND PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT 
						SELECT  @GROSS_DIE = GROSS_DIE FROM #INVENTORY_SUMMARY_TABLE WHERE PS_TYPE = @PS_TYPE
						AND PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT 
						
						SET @DIE_TOTAL_FOR_WAFERS = @QTY * ISNULL(@GROSS_DIE,0)

						UPDATE @RESULTS SET [QTY STR] = ISNULL([QTY STR],'') + ','+CONVERT(VARCHAR,@QTY) ,[WAFER DIE QTY] = ISNULL([WAFER DIE QTY],0) + @DIE_TOTAL_FOR_WAFERS
						WHERE [PART NUMBER] = @PART_NUMBER AND [BASE PRODUCT] = @BASE_PRODUCT
				END
				ELSE
				BEGIN
						
						SELECT @QTY = (ISNULL(SUM(QTY_IN),0) + ISNULL(SUM(TRA_QTY_IN),0 ) ) FROM #INVENTORY_SUMMARY_TABLE WHERE PS_TYPE = @PS_TYPE
						AND PART_NUMBER = @PART_NUMBER AND BASE_PRODUCT = @BASE_PRODUCT
			
						
						UPDATE @RESULTS SET [QTY STR] = ISNULL([QTY STR],'') + ',' + CONVERT(VARCHAR,@QTY),[DIE QTY] = ISNULL([DIE QTY],0) + @QTY
						WHERE [PART NUMBER] = @PART_NUMBER AND [BASE PRODUCT] = @BASE_PRODUCT
				END		
			SET @QTY = NULL
			SET @PS_TYPE = NULL
			SET @PS_TYPE_NAME = NULL
			END
		END

		
		SET @BASE_PRODUCT	= NULL
		SET @PART_NUMBER 	= NULL
		SET @WAFER_QTY_IN 	= NULL
		SET @QTY_IN 		= NULL
		SET @TRA_WAFER_QTY_IN = NULL
		SET @TRA_QTY_IN		= NULL
		FETCH NEXT FROM @LOT_CURSOR INTO @BASE_PRODUCT,@PART_NUMBER
	END
 	CLOSE @LOT_CURSOR
 	DEALLOCATE @LOT_CURSOR

	SELECT 
		[BASE PRODUCT],[PART NUMBER],[QTY STR],
		( ISNULL([WAFER DIE QTY],0) ) as [DIE TOTAL FOR WAFERS],
		(ISNULL([DIE QTY],0) ) AS [TOTAL DIE QTY]
	FROM @RESULTS 
	ORDER BY [BASE PRODUCT],[PART NUMBER]
END
